﻿<!doctype html>
<html>
<head>
    <title>SpreadJS - Pareto Sparkline</title>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />

    <link href="../../../external/spreadjs/css/gcspread.sheets.excel2013white.9.40.20161.0.css" rel="stylesheet" type="text/css" />

    <script src="../../../external/external/jquery-1.8.2.min.js" type="text/javascript"></script>

    <script type="text/javascript" src="../../../external/spreadjs/gcspread.sheets.all.9.40.20161.0.min.js"></script>

    <script id="scriptInit" type="text/javascript">
        /*code_begin*/
        var spreadNS = GcSpread.Sheets, spread;
        $(document).ready(function () {
            spread = new GcSpread.Sheets.Spread(document.getElementById("ss"), { sheetCount: 2 });
            initSpread(spread);

            $("#labelType").change(applyChanges);
            $("#highlightPosition").change(applyChanges);
        });

        function applyChanges() {
            var sheet = spread.getActiveSheet();
            var labelType = parseInt($("#labelType").val(), 10),
                highlightPosition = parseInt($("#highlightPosition").val(), 10);

            if (!isNaN(labelType) && !isNaN(highlightPosition)) {
                var formulaPart1, formulaPart2;

                if (spread.getActiveSheetIndex() == 0) {
                    formulaPart1 = "=PARETOSPARKLINE(B2:B7,",
                    formulaPart2 = ",C2:C7,B8,B9," + highlightPosition + "," + labelType + ",false)";
                    for (var i = 1; i <= 6; i++) {
                        var f = formulaPart1 + i + formulaPart2;
                        sheet.setFormula(i, 3, formulaPart1 + i + formulaPart2);
                    }
                } else {
                    formulaPart1 = "=PARETOSPARKLINE(A3:F3,",
                    formulaPart2 = ",A4:F4,B8,B9," + highlightPosition + "," + labelType + ",true)";
                    for (var i = 1; i <= 6; i++) {
                        var f = formulaPart1 + i + formulaPart2;
                        sheet.setFormula(4, i - 1, formulaPart1 + i + formulaPart2);
                    }
                }
            }
        }

        function initSpread(spread) {
            spread.newTabVisible(false);
            this.initHorizontalSparkline(spread.sheets[0], "Horizontal");
            this.initVerticalSparkline(spread.sheets[1], "Vertical");
        };

        function initHorizontalSparkline(sheet, name) {
            sheet.isPaintSuspended(true);
            sheet.setName(name);
            sheet.addSpan(0, 0, 1, 4);
            sheet.getCell(0, 0).value("The Reason of Being Late")
                .font("20px Arial")
                .hAlign(spreadNS.HorizontalAlign.center)
                .vAlign(spreadNS.VerticalAlign.center)
                .backColor("purple")
                .foreColor("white");
            sheet.setBorder(new spreadNS.Range(1, 2, 6, 1),
                new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin),
                { inside: true }, spreadNS.SheetArea.viewport);
            sheet.setValue(1, 0, "Traffic");
            sheet.setValue(2, 0, "Child care");
            sheet.setValue(3, 0, "Public transportation");
            sheet.setValue(4, 0, "Weather");
            sheet.setValue(5, 0, "Overslept");
            sheet.setValue(6, 0, "Emergency");
            sheet.setValue(7, 0, "target");
            sheet.setValue(8, 0, "target2");
            sheet.setValue(1, 1, 20);
            sheet.setValue(2, 1, 15);
            sheet.setValue(3, 1, 13);
            sheet.setValue(4, 1, 5);
            sheet.setValue(5, 1, 4);
            sheet.setValue(6, 1, 1);
            sheet.setValue(7, 1, 0.5);
            sheet.setValue(8, 1, 0.8);

            sheet.setValue(1, 2, "#FF1493");
            sheet.setValue(2, 2, "#FFE7BA");
            sheet.setValue(3, 2, "#FFD700");
            sheet.setValue(4, 2, "#FFAEB9");
            sheet.setValue(5, 2, "#FF8C69");
            sheet.setValue(6, 2, "#FF83FA");

            sheet.addSpan(7, 2, 2, 2);
            sheet.getCell(7, 2).wordWrap(true);
            sheet.setValue(7, 2, 'Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".');

            sheet.setColumnWidth(0, 140);
            sheet.setColumnWidth(1, 80);
            sheet.setColumnWidth(2, 80);
            sheet.setColumnWidth(3, 340);
            sheet.setRowHeight(0, 30);
            sheet.setRowHeight(1, 30);
            sheet.setRowHeight(2, 30);
            sheet.setRowHeight(3, 30);
            sheet.setRowHeight(4, 30);
            sheet.setRowHeight(5, 30);
            sheet.setRowHeight(6, 30);
            sheet.setRowHeight(7, 30);

            sheet.setFormula(1, 3, '=PARETOSPARKLINE(B2:B7,1,C2:C7,B8,B9,4,2,false)');
            sheet.setFormula(2, 3, '=PARETOSPARKLINE(B2:B7,2,C2:C7,B8,B9,4,2,false)');
            sheet.setFormula(3, 3, '=PARETOSPARKLINE(B2:B7,3,C2:C7,B8,B9,4,2,false)');
            sheet.setFormula(4, 3, '=PARETOSPARKLINE(B2:B7,4,C2:C7,B8,B9,4,2,false)');
            sheet.setFormula(5, 3, '=PARETOSPARKLINE(B2:B7,5,C2:C7,B8,B9,4,2,false)');
            sheet.setFormula(6, 3, '=PARETOSPARKLINE(B2:B7,6,C2:C7,B8,B9,4,2,false)');

            sheet.setText(0, 1, "Points", spreadNS.SheetArea.colHeader);
            sheet.setText(0, 2, "Color range", spreadNS.SheetArea.colHeader);
            sheet.isPaintSuspended(false);
        }

        function initVerticalSparkline(sheet, name) {
            sheet.isPaintSuspended(true);
            sheet.setName(name);

            sheet.addSpan(0, 0, 1, 6);
            sheet.getCell(0, 0).value("The Reason of Being Late")
                .font("20px Arial")
                .hAlign(spreadNS.HorizontalAlign.center)
                .vAlign(spreadNS.VerticalAlign.center)
                .backColor("purple")
                .foreColor("white");
            sheet.setBorder(new spreadNS.Range(4, 0, 1, 6),
                new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin),
                { inside: true }, spreadNS.SheetArea.viewport);
            sheet.setValue(1, 0, "Traffic");
            sheet.setValue(1, 1, "Child care");
            sheet.setValue(1, 2, "Public transportation");
            sheet.setValue(1, 3, "Weather");
            sheet.setValue(1, 4, "Overslept");
            sheet.setValue(1, 5, "Emergency");
            sheet.setValue(7, 0, "target");
            sheet.setValue(8, 0, "target2");
            sheet.setValue(2, 0, 20);
            sheet.setValue(2, 1, 15);
            sheet.setValue(2, 2, 13);
            sheet.setValue(2, 3, 5);
            sheet.setValue(2, 4, 4);
            sheet.setValue(2, 5, 1);
            sheet.setValue(7, 1, 0.5);
            sheet.setValue(8, 1, 0.8);

            sheet.setValue(3, 0, "#FF1493");
            sheet.setValue(3, 1, "#FFE7BA");
            sheet.setValue(3, 2, "#FFD700");
            sheet.setValue(3, 3, "#FFAEB9");
            sheet.setValue(3, 4, "#FF8C69");
            sheet.setValue(3, 5, "#FF83FA");

            sheet.addSpan(7, 2, 2, 4);
            sheet.getCell(7, 2).wordWrap(true);
            sheet.setValue(7, 2, 'Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".');

            sheet.setColumnWidth(0, 80);
            sheet.setColumnWidth(1, 100);
            sheet.setColumnWidth(2, 140);
            sheet.setColumnWidth(3, 100);
            sheet.setColumnWidth(4, 100);
            sheet.setColumnWidth(5, 100);
            sheet.setRowHeight(4, 160);
            sheet.setRowHeight(7, 30);
            sheet.setRowHeight(8, 30);

            sheet.setFormula(4, 0, '=PARETOSPARKLINE(A3:F3,1,A4:F4,B8,B9,4,2,true)');
            sheet.setFormula(4, 1, '=PARETOSPARKLINE(A3:F3,2,A4:F4,B8,B9,4,2,true)');
            sheet.setFormula(4, 2, '=PARETOSPARKLINE(A3:F3,3,A4:F4,B8,B9,4,2,true)');
            sheet.setFormula(4, 3, '=PARETOSPARKLINE(A3:F3,4,A4:F4,B8,B9,4,2,true)');
            sheet.setFormula(4, 4, '=PARETOSPARKLINE(A3:F3,5,A4:F4,B8,B9,4,2,true)');
            sheet.setFormula(4, 5, '=PARETOSPARKLINE(A3:F3,6,A4:F4,B8,B9,4,2,true)');

            sheet.isPaintSuspended(false);
        }
        /*code_end*/
    </script>
</head>
<body>
    <div class="sample-turtorial">
        <div id="ss" style="width: 100%; height: 420px; border: 1px solid gray;"></div>

        <div class="demo-options">
            <div class="option-row">
                <label for="labelType">Label type:</label>
                <select id="labelType">
                    <option value="0">None</option>
                    <option value="1">Cumulated percentage</option>
                    <option value="2" selected="selected">Single percentage</option>
                </select>
            </div>
            <div class="option-row">
                <label for="highlightPosition" >Highlight item:</label>
                <select id="highlightPosition">
                    <option value="0">None</option>
                    <option value="1">1st</option>
                    <option value="2">2nd</option>
                    <option value="3">3rd</option>
                    <option value="4" selected="selected">4th</option>
                    <option value="5">5th</option>
                    <option value="6">6th</option>
                </select>
            </div>
        </div>
    </div>
</body>
</html>
